﻿/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 *
 * All rights reserved.
 *
 * EPPlus is an Open Source project provided under the
 * GNU General Public License (GPL) as published by the
 * Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
 *
 * EPPlus provides server-side generation of Excel 2007 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 *
 *
 * The GNU General Public License can be viewed at http://www.opensource.org/licenses/gpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 *
 * The code for this project may be used and redistributed by any means PROVIDING it is
 * not sold for profit without the author's written consent, and providing that this notice
 * and the author's name and all copyright notices remain intact.
 *
 * All code and executables are provided "as is" with no warranty either express or implied.
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 *
 * Code change notes:
 *
 * Author							Change						Date
 *******************************************************************************
 * Jan Källman		Added		10-SEP-2009
 *******************************************************************************/

/*
 * Sample code demonstrating how to generate Excel spreadsheets on the server using
 * Office Open XML and the ExcelPackage wrapper classes.
 *
 * ExcelPackage provides server-side generation of Excel 2007 spreadsheets.
 * See http://www.codeplex.com/ExcelPackage for details.
 *
 * Sample 3: Creates a workbook based on a template and populates using the database data.
 *
 * Copyright 2007 © Dr John Tunnicliffe
 * mailto:dr.john.tunnicliffe@btinternet.com
 * All rights reserved.
 *
 * All code and executables are provided "as is" with no warranty either express or implied.
 * The author accepts no liability for any damage or loss of business that this product may cause.
 */

using System;
using System.Drawing;
using System.IO;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace EPPlus.Core.Tests.SampleApp
{
    [TestClass]
    public class Sample03
    {
        /// <summary>
        /// Sample 3 - creates a workbook and populates using data from the AdventureWorks database
        /// This sample requires the AdventureWorks database.
        /// This one is from the orginal Excelpackage sample project, but without the template
        /// </summary>
        [TestMethod]
        public void RunSample3()
        {
            string file = Path.Combine("bin", "sample3.xlsx");

            // ok, we can run the real code of the sample now
            var newFile = new FileInfo(file);
            if(newFile.Exists)
            {
                newFile.Delete();
            }

            using (ExcelPackage xlPackage = new ExcelPackage(newFile))
            {
                // uncomment this line if you want the XML written out to the outputDir
                //xlPackage.DebugMode = true;

                // get handle to the existing worksheet
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Sales");
                var namedStyle = xlPackage.Workbook.Styles.CreateNamedStyle("HyperLink");   //This one is language dependent
                namedStyle.Style.Font.UnderLine = true;
                namedStyle.Style.Font.Color.SetColor(Color.Blue);
                if (worksheet != null)
                {
                    const int startRow = 5;
                    int row = startRow;
                    //Create Headers and format them
                    worksheet.Cells["A1"].Value = "AdventureWorks Inc.";
                    using (ExcelRange r = worksheet.Cells["A1:G1"])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("Britannic Bold", 22, FontStyle.Italic));
                        r.Style.Font.Color.SetColor(Color.White);
                        r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
                        r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
                    }
                    worksheet.Cells["A2"].Value = "Year-End Sales Report";
                    using (ExcelRange r = worksheet.Cells["A2:G2"])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Italic));
                        r.Style.Font.Color.SetColor(Color.Black);
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
                        r.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
                    }

                    worksheet.Cells["A4"].Value = "Name";
                    worksheet.Cells["B4"].Value = "Job Title";
                    worksheet.Cells["C4"].Value = "Region";
                    worksheet.Cells["D4"].Value = "Monthly Quota";
                    worksheet.Cells["E4"].Value = "Quota YTD";
                    worksheet.Cells["F4"].Value = "Sales YTD";
                    worksheet.Cells["G4"].Value = "Quota %";
                    worksheet.Cells["A4:G4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                    worksheet.Cells["A4:G4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
                    worksheet.Cells["A4:G4"].Style.Font.Bold = true;

                    var rnd = new Random();
                    while (row < 50)
                    {
                        int col = 1;
                        // our query has the columns in the right order, so simply
                        // iterate through the columns
                        for (int i = 0; i < 7; i++)
                        {
                            // use the email address as a hyperlink for column 1
                            if (i == 1)
                            {
                                // insert the email address as a hyperlink for the name
                                string hyperlink = "mailto:" + "user@site.com";
                                worksheet.Cells[row, 1].Hyperlink = new Uri(hyperlink, UriKind.Absolute);
                            }
                            else
                            {
                                // do not bother filling cell with blank data (also useful if we have a formula in a cell)
                                worksheet.Cells[row, col].Value = rnd.Next();
                                col++;
                            }
                        }
                        row++;
                    }

                    worksheet.Cells[startRow, 1, row - 1, 1].StyleName = "HyperLink";
                    worksheet.Cells[startRow, 4, row - 1, 6].Style.Numberformat.Format = "[$$-409]#,##0";
                    worksheet.Cells[startRow, 7, row - 1, 7].Style.Numberformat.Format = "0%";

                    worksheet.Cells[startRow, 7, row - 1, 7].FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-1]/RC[-2])";

                    //Set column width
                    worksheet.Column(1).Width = 25;
                    worksheet.Column(2).Width = 28;
                    worksheet.Column(3).Width = 18;
                    worksheet.Column(4).Width = 12;
                    worksheet.Column(5).Width = 10;
                    worksheet.Column(6).Width = 10;
                    worksheet.Column(7).Width = 12;

                    // lets set the header text
                    worksheet.HeaderFooter.OddHeader.CenteredText = "AdventureWorks Inc. Sales Report";
                    // add the page number to the footer plus the total number of pages
                    worksheet.HeaderFooter.OddFooter.RightAlignedText =
                        string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
                    // add the sheet name to the footer
                    worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
                    // add the file path to the footer
                    worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
                }
                // we had better add some document properties to the spreadsheet

                // set some core property values
                xlPackage.Workbook.Properties.Title = "Sample 3";
                xlPackage.Workbook.Properties.Author = "John Tunnicliffe";
                xlPackage.Workbook.Properties.Subject = "ExcelPackage Samples";
                xlPackage.Workbook.Properties.Keywords = "Office Open XML";
                xlPackage.Workbook.Properties.Category = "ExcelPackage Samples";
                xlPackage.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 file from scratch using the Packaging API and Office Open XML";

                // set some extended property values
                xlPackage.Workbook.Properties.Company = "AdventureWorks Inc.";
                xlPackage.Workbook.Properties.HyperlinkBase = new Uri("http://www.codeplex.com/MSFTDBProdSamples");

                // set some custom property values
                xlPackage.Workbook.Properties.SetCustomPropertyValue("Checked by", "John Tunnicliffe");
                xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147");
                xlPackage.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "ExcelPackage");

                // save the new spreadsheet
                xlPackage.Save();
            }
        }
    }
}